package com.wanmait.travel.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.wanmait.travel.util.DbHelper;
import com.wanmait.travel.vo.Scenic;
import com.wanmait.travel.vo.ScenicTickitType;
import com.wanmait.travel.vo.Tickit;
import com.wanmait.travel.vo.TickitType;

public class TickitDAO {
	private static TickitDAO tickitDAO;
	public static TickitDAO getDAO() {
		if(tickitDAO==null){
			tickitDAO=new TickitDAO();
			return tickitDAO;
		}
		return tickitDAO;
	}
	private TickitDAO() {}
	
	
	public Scenic findAll(int id)
	{
		String sql="select * from scenic where id=?";
		Connection conn= DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		Scenic scenic=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, id);
			rs=ps.executeQuery();
			while(rs.next())
			{
				scenic=new Scenic();
				scenic.setId(rs.getInt("id"));
				scenic.setName(rs.getString("name"));
				scenic.setLocation(rs.getString("location"));
				scenic.setOpenTime(rs.getString("open_time"));
				scenic.setCloseTime(rs.getString("close_time"));
				scenic.setAnding(rs.getInt("anding"));
				scenic.setIntro(rs.getString("intro"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DbHelper.close(rs, ps, conn);
		}
		return scenic;
		
	}
	
 public ScenicTickitType findByTickit(Scenic scenic)
 {
	
	 ScenicTickitType stt=new ScenicTickitType();
	 stt.setScenic(scenic);
	 String sql="SELECT scenic.*, tickit.*,tickit_type.name AS tickit_type_name  FROM tickit"
			+" LEFT JOIN scenic ON tickit.scenic_id=scenic.id"
			+" LEFT JOIN tickit_type ON tickit.tickit_type_id=tickit_type.id where tickit.scenic_id=?";
	 Connection conn=DbHelper.getConn();
	 PreparedStatement ps=null;
	 ResultSet rs=null;
	 try {
		 ps=conn.prepareStatement(sql);
		 ps.setObject(1, scenic.getId());
		rs=ps.executeQuery();
		 while(rs.next())
		 {
			 
			Tickit tickit=new Tickit();
			tickit.setId(rs.getInt("id"));
			tickit.setNum(rs.getInt("num"));
			tickit.setPrice(rs.getDouble("price"));
			tickit.setScenicId(rs.getInt("scenic_id"));
			tickit.setTickitTypeId(rs.getInt("tickit_type_id"));
			TickitType tickitType=new TickitType();
			tickitType.setId(rs.getInt("id"));
			tickitType.setType(rs.getString("tickit_type_name"));
			tickit.setTickitType(tickitType);
			if(tickit.getTickitType().getType().equals("成人票"))
			{
				stt.setTickitMan(tickit);
			}
			if(tickit.getTickitType().getType().equals("儿童票"))
			{
				stt.setTickitChild(tickit);
			}
			if(tickit.getTickitType().getType().equals("老年票"))
			{
				stt.setTickitOld(tickit);
			}
			
		 }
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	 finally
		{
			DbHelper.close(rs, ps, conn);
		}
	
	return stt;
	  }
}
